import json

import requests
import pandas as pd
import os
from urllib.parse import urlparse
from io import BytesIO
import re


def download_and_read_excel(url):
    """
    通过URL下载文件，识别是否为Excel类型，并读取第一张表的数据



    Returns:
        dict: 包含处理结果的字典
    """
    result = {
        'success': False,
        'is_excel': False,
        'filename': None,
        'data': None,
        'error': None,
        'file_info': {}
    }

    try:
        # 1. 下载文件
        print(f"正在下载文件: {url}")
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
        }

        response = requests.get(url, headers=headers, stream=True, timeout=30)
        response.raise_for_status()

        # 2. 获取文件名和基本信息
        filename = get_filename_from_response(url, response)
        file_size = len(response.content)

        result['filename'] = filename
        result['file_info'] = {
            'size_bytes': file_size,
            'size_mb': round(file_size / (1024 * 1024), 2),
            'content_type': response.headers.get('content-type', '')
        }
        print(f"文件名: {filename}")
        print(f"文件大小: {result['file_info']['size_mb']} MB")
        # 3. 识别是否为Excel文件
        is_excel = is_excel_file(filename, response.headers)
        result['is_excel'] = is_excel
        print(f"是否为Excel文件: {is_excel}")
        if not is_excel:
            result['error'] = f"文件 '{filename}' 不是Excel格式"
            return result
        # 4. 读取Excel数据
        file_content = BytesIO(response.content)
        try:
            # 先获取所有sheet名称
            excel_file = pd.ExcelFile(file_content)
            sheet_names = excel_file.sheet_names
            print(f"Excel包含 {len(sheet_names)} 个工作表: {sheet_names}")
            # 读取第一张表
            first_sheet = sheet_names[0]

            data = pd.read_excel(file_content, sheet_name=0)

            return DataProcessing(data)

        except Exception as e:
            result['error'] = f"读取Excel文件时出错: {str(e)}"
            print(result['error'])

    except requests.RequestException as e:
        result['error'] = f"下载文件时出错: {str(e)}"
        print(result['error'])
    except Exception as e:
        result['error'] = f"处理文件时出错: {str(e)}"
        print(result['error'])
    return result

def get_filename_from_response(url, response):
    """从URL或响应头中获取文件名"""

    # 1. 尝试从响应头获取文件名
    content_disposition = response.headers.get('content-disposition', '')
    if content_disposition:
        filename_match = re.findall(r'filename[^;=\n]*=(([\'"]).*?\2|[^;\n]*)', content_disposition)
        if filename_match:
            filename = filename_match[0][0].strip('\'"')
            if filename:
                return filename

    # 2. 从URL中提取文件名
    parsed_url = urlparse(url)
    filename = os.path.basename(parsed_url.path)

    # 3. 如果文件名不包含扩展名，尝试根据content-type添加
    if filename and '.' not in filename:
        content_type = response.headers.get('content-type', '').lower()
        if 'spreadsheetml' in content_type or 'excel' in content_type:
            filename += '.xlsx'

    # 4. 默认文件名
    if not filename:
        filename = 'downloaded_excel_file.xlsx'

    return filename

def is_excel_file(filename, headers):
    """判断文件是否为Excel类型"""

    # 1. 通过文件扩展名判断
    excel_extensions = {'.xlsx', '.xls', '.xlsm', '.xlsb'}
    file_ext = os.path.splitext(filename.lower())[1]

    if file_ext in excel_extensions:
        return True

    # 2. 通过MIME类型判断
    content_type = headers.get('content-type', '').lower()
    excel_mime_types = [
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',  # .xlsx
        'application/vnd.ms-excel',  # .xls
        'application/vnd.ms-excel.sheet.macroenabled.12',  # .xlsm
        'application/vnd.ms-excel.sheet.binary.macroenabled.12'  # .xlsb
    ]

    return any(mime_type in content_type for mime_type in excel_mime_types)

def display_excel_data(result):
    """显示Excel数据的详细信息"""

    if not result['success'] or result['data'] is None:
        print(f"无法显示数据: {result.get('error', '未知错误')}")
        return

    data = result['data']
    file_info = result['file_info']

    print("\n" + "=" * 60)
    print("�� EXCEL数据分析报告")
    print("=" * 60)

    print(f"�� 文件名: {result['filename']}")
    print(f"�� 文件大小: {file_info['size_mb']} MB")
    print(f"�� 工作表: {file_info['sheet_names']}")
    print(f"�� 当前表: {file_info['first_sheet']}")
    print(f"�� 数据维度: {file_info['rows']} 行 × {file_info['columns']} 列")

    print(f"�� 列名:")
    for i, col in enumerate(file_info['column_names'], 1):
        print(f"  {i:2d}. {col}")

    print(f"�� 数据类型:")
    for col, dtype in data.dtypes.items():
        print(f"  {col}: {dtype}")

    print(f"\n�� 前5行数据预览:")
    print(data.head().to_string())

    if len(data) > 5:
        print(f"\n�� 数值列统计:")
        numeric_data = data.select_dtypes(include=['number'])
        if not numeric_data.empty:
            print(numeric_data.describe().to_string())

    print("=" * 60)

def DataProcessing(data):
    # 遍历每一行数据
    hang=None
    third_row = data.iloc[1]
    body=[]

    for index, row in data.iterrows():
        if row['关键技术参数']=="合计":
            hang=index

    selected_data = data.iloc[2:hang]  # 选择第1列到第4列（索引0到3）

    for index, row in selected_data.iterrows():
        datamap={}
        # 遍历当前行的每一列

        for col_name, value in row.items():

            if str(value) not in "nan":
                datamap[str(third_row[col_name]).replace("\n", "")] = str(value)
        body.append(datamap)
    print(body)
    return body